package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.InterestRateDao;
import csc.fresher.entity.InterestRate;
import csc.fresher.entity.SavingAccountHasInterestRate;
import csc.fresher.entity.SavingInterestRate;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class SavingInterestRateDaoImpl implements InterestRateDao{

	private static final Logger logger = Logger
			.getLogger(PeriodTypeDaoImpl.class);

	@Autowired
	DataSource dataSource;
	
	@Override
	public void insert(InterestRate interestRate) {
		// TODO Auto-generated method stub
		String query = "INSERT INTO `SavingInterestRate`(`SavingInterestRateID`,`createdDate`,`Rate`, `CurrencyID`, `PeriodTypeID`,`FromDate`,`ToDate`) VALUES (?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { interestRate.getInterestRateID(), interestRate.getCreatedDate(), interestRate.getRate(), interestRate.getCurrencyID(),interestRate.getPeriodTypeID(), interestRate.getFromDate(), interestRate.getToDate() });
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}
//not process
	@Override
	public void update(InterestRate interestRate) {
		// TODO Auto-generated method stub
		String query = "UPDATE `SavingInterestRate` SET `SavingInterestRate`.`Rate` = ?, SET `SavingInterestRate`.`CurrencyID`=?, SET `SavingInterestRate`.`PeriodTypeID`=?, SET `SavingInterestRate`.`FromDate`=?, SET `SavingInterestRate`.`ToDate`=? WHERE `SavingInterestRate`.`SavingInterestRateID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			DatabaseUtil.executeUpdate(
					preparedStatement,
					connection,
					query,
					new Object[] { interestRate.getRate(),
							interestRate.getCurrencyID(),
							interestRate.getPeriodTypeID(),
							interestRate.getFromDate(),
							interestRate.getToDate(),
							interestRate.getInterestRateID() });
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public InterestRate getInterestRateByID(int interestRateID) {
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`SavingInterestRateID`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { interestRateID });
			interestRate = new SavingInterestRate();
			if(rs.next()){
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRate;
	}
// function get
	/*private SavingInterestRate getSavingInterestResultSet(ResultSet rs) throws SQLException{
		SavingInterestRate savingInterestRate = new SavingInterestRate();
		savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
		savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
		savingInterestRate.setRate(rs.getDouble("Rate"));
		savingInterestRate.setCurrencyID(rs.getInt("Currency_CurrencyID"));
		savingInterestRate.setPeriodTypeID(rs.getInt("PeriodType_PeriodTypeID"));
		savingInterestRate.setFromDate(rs.getDate("FromDate"));
		savingInterestRate.setFromDate(rs.getDate("ToDate"));
		return savingInterestRate;
	}
	*/
	@Override
	public InterestRate getInterestRateByType(int currencyID, int periodTypeID) {
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`CurrencyID`=? AND `SavingInterestRate`.`PeriodTypeID`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[] { currencyID, periodTypeID });
			interestRate = new SavingInterestRate();
			if(rs.next()){
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRate;
		
	}

	@Override
	public List<InterestRate> getAllInterestRates () {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate`";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			interestRates = new ArrayList<>();
			while(rs.next()){
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getDouble("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}

	@Override
	public List<InterestRate> getListInterestRates(int startIndex, int maxReturn) {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` LIMIT ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			interestRates = new ArrayList<>();
			while(rs.next()){
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getFloat("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}

	@Override
	public int totalInterestRates() {
		// TODO Auto-generated method stub
		return getAllInterestRates().size();
	}
	@Override
	public SavingAccountHasInterestRate getInterestRateByAccountNumberID(long accountNumberID) {
		String query = "SELECT SavingInterestRateID, si.`createdDate`, CurrencyID, PeriodTypeID, Rate, si.`FromDate` AS siFromDate,  si.`ToDate` AS siToDate, shi.* "
				+ " FROM `savingaccount` sa LEFT JOIN `savingaccount_has_interestrate` shi"
				+ " ON sa.`SavingAccountNumber` = shi.`SavingAccount_AccNumber`"
				+ " LEFT JOIN `savinginterestrate` si"
				+ " ON si.`SavingInterestRateID` = shi.`InterestRate_InterestRateID`"
				+ " WHERE sa.`SavingAccountNumber`= ? AND shi.`State` = 'accepted' "
				+ " ORDER BY si.`FromDate` DESC ";
		SavingAccountHasInterestRate savingAccountHasInterestRate = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, accountNumberID);
			rs = preparedStatement.executeQuery();
			if (rs.next()){
				SavingInterestRate savingInterestRate = new SavingInterestRate();
				savingInterestRate.setCreatedDate(rs.getDate("createdDate"));
				savingInterestRate.setCurrencyID(rs.getInt("CurrencyID"));
				savingInterestRate.setFromDate(rs.getDate("siFromDate"));
				savingInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingInterestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				savingInterestRate.setRate(rs.getDouble("Rate"));
				savingInterestRate.setToDate(rs.getDate("siToDate"));
				
				savingAccountHasInterestRate = new SavingAccountHasInterestRate();
				savingAccountHasInterestRate.setAccountNumber(rs.getLong("SavingAccount_AccNumber"));
				savingAccountHasInterestRate.setBalance(rs.getDouble("Balance"));
				savingAccountHasInterestRate.setFromDate(rs.getTimestamp("FromDate"));
				savingAccountHasInterestRate.setToDate(rs.getTimestamp("siToDate"));
				savingAccountHasInterestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				savingAccountHasInterestRate.setInterestRate(savingInterestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs,
					logger);
		}
		return savingAccountHasInterestRate;
	}
	//@Override
	public List<InterestRate> getAllInterestRateByCurrencyID(int currencyID) {
		List<InterestRate> interestRates = null;
		InterestRate interestRate = null;
		String query = "SELECT * FROM `SavingInterestRate` WHERE `SavingInterestRate`.`CurrencyID`=?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, new Object[]{currencyID});
			interestRates = new ArrayList<>();
			while(rs.next()){
				interestRate = new SavingInterestRate();
				interestRate.setCreatedDate(rs.getDate("createdDate"));
				interestRate.setCurrencyID(rs.getInt("CurrencyID"));
				interestRate.setFromDate(rs.getDate("FromDate"));
				interestRate.setInterestRateID(rs.getInt("SavingInterestRateID"));
				interestRate.setPeriodTypeID(rs.getInt("PeriodTypeID"));
				interestRate.setRate(rs.getFloat("Rate"));
				interestRate.setToDate(rs.getDate("ToDate"));
				interestRates.add(interestRate);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return interestRates;
	}
	
	

}
